# 导入 xlrd 库

import pymongo
import xlrd


def genId(jihao):
    res = jihao.split('-')
    id = res[0]+res[1]+str(float(res[2])*10).zfill(3)[:-2]
    print(id)
    return id



def readFile(filename):
    workbook = xlrd.open_workbook(filename)
    sheet = workbook.sheet_by_index(0)
    documents = []
    for i in range(2, sheet.nrows):

        if (sheet.cell_value(i, 1)) and '内部资料' not in sheet.cell_value(i, 1) :
            print(sheet.cell_value(i, 1))
            row = []
            for j in [1, 2, 3, 4, 13, 25, 26, 27, 28, 29]:
                try:
                    row.append(sheet.cell_value(i, j))


                except IndexError:
                    print('list index out of range', row)
                    return
        rowText = {
            'order':genId(row[0].replace('/', '-')),
            '机型': row[0].replace('/', '-'),  # 2-14/10 ==>
            '用户': row[1],
            '生产编号': row[2],
            '图号': ''.join([row[3], 'ZT']),
            '联轴器': row[4],
            'I级轮毂装配': row[5],
            'II级轮毂装配': row[6],
            '伺服控制装置': row[7],
            '芯轴': row[8],
            '主轴承装配': row[9],
        }
        documents.append(rowText)

    # for index in range(workbook.nsheets):
    #     sheet = workbook.sheet_by_index(index)
    #     li.append(readSheetAll(sheet))
    return documents


def saveTodb(collection, documents):
    myclient = pymongo.MongoClient('mongodb://192.168.10.36:27017/')
    mydb = myclient['pctt']
    mycol = mydb[collection]
    mycol.delete_many({})
    x = mycol.insert_many(documents)
    print(x.inserted_ids)


if __name__ == '__main__':
    documents = readFile('./AP动叶可调轴流风机明细表-新.xls')
    saveTodb('mx', documents)
